﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DimensionalMeasurement
{
    public partial class FrmQtSql : Form
    {
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void showUI(IntPtr parent);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void setUISize(int width, int height);

        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void selectAllAPI(string tablename, int showcount);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void selectByTimeAPI(string tablename, string columnname, int showcount, string starttime, string endtime);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void selectByContentAPI(string tablename, string columnname, int showcount, string content);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void selectByColAPI(string tablename, string columnname, int showcount, string content1, string content2);

        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool deleteByTimeAPI(string tablename, string columnname, string starttime, string endtime);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool deleteByContentAPI(string tablename, string columcolumnnamenname, string content);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool deleteByColAPI(string tablename, string columnname, string content1, string content2);

        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void getAllCol(string tablename, out IntPtr columnname);

        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void getAllTable(string dbname, out IntPtr tablename);

        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool insertDataAPI(string tablename, byte[] insertdata);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool updateDataAPI(string tablename, string columnname, byte[] updatedata, string ID);

        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool createTable(string tablename);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool dropTable(string tablename);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern void getAllDB(out IntPtr dbname);
        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool initDBAPI(string ip, int port, string username, string password);

        [DllImport("mysql.dll", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
        public static extern bool openDBAPI(string dbname);
        public FrmQtSql()
        {
            InitializeComponent();
            dp_startTime.Value = DateTime.Now.AddDays(-7);
            dp_endTime.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second);

        }

        private float X;//当前窗体的宽度
        private float Y;//当前窗体的高度

        /// <summary>
        /// 将控件的宽，高，左边距，顶边距和字体大小暂存到tag属性中
        /// </summary>
        /// <param name="cons">递归控件中的控件</param>
        private void setTag(Control cons)
        {
            foreach (Control con in cons.Controls)
            {
                con.Tag = con.Width + ":" + con.Height + ":" + con.Left + ":" + con.Top + ":" + con.Font.Size;
                if (con.Controls.Count > 0)
                    setTag(con);
            }
        }

        //根据窗体大小调整控件大小
        private void setControls(float newx, float newy, Control cons)
        {
            //遍历窗体中的控件，重新设置控件的值
            foreach (Control con in cons.Controls)
            {

                string[] mytag = con.Tag.ToString().Split(new char[] { ':' });//获取控件的Tag属性值，并分割后存储字符串数组
                float a = System.Convert.ToSingle(mytag[0]) * newx;//根据窗体缩放比例确定控件的值，宽度
                con.Width = (int)a;//宽度
                a = System.Convert.ToSingle(mytag[1]) * newy;//高度
                con.Height = (int)(a);
                a = System.Convert.ToSingle(mytag[2]) * newx;//左边距离
                con.Left = (int)(a);
                a = System.Convert.ToSingle(mytag[3]) * newy;//上边缘距离
                con.Top = (int)(a);
                Single currentSize = System.Convert.ToSingle(mytag[4]) * newy;//字体大小
                con.Font = new Font(con.Font.Name, currentSize, con.Font.Style, con.Font.Unit);
                if (con.Controls.Count > 0)
                {
                    setControls(newx, newy, con);
                }
            }
        }

        private void FrmQtSql_Load(object sender, EventArgs e)
        {
            try
            {
                button1.Focus();
                showUI(this.panel2.Handle);//绑定控件
                setUISize(panel2.Width, panel2.Height);//适应分辨率

                if (!ConnectDatabase()) //连接数据库
                {
                    return;
                }
                GetAllDbs();
            }
            catch (Exception ex)
            {
                this.lblStatus.ForeColor = Color.Red;
                this.lbl_show.ForeColor = Color.Red;
                this.lbl_show.Text = "数据库连接异常：";
                Tool.LogForm(ex);
            }
            X = this.Width;//获取窗体的宽度
            Y = this.Height;//获取窗体的高度
            setTag(this);//调用方法

        }
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        private bool GetAllDbs()
        {
            getAllDB(out IntPtr dbnames);
            string strDbs = Marshal.PtrToStringAnsi(dbnames);
            if (!string.IsNullOrEmpty(strDbs))
            {
                string[] strDb = strDbs.Split(' ');
                for (int i = 0; i < strDb.Length - 1; i++)
                {
                    cbx_GetDbs.Items.Add(strDb[i]);
                }
                return true;
            }
            return false;
        }
        /// <summary>
        /// 获取选定库中的所有数据表
        /// </summary>
        /// <param name="databaseName"></param>
        private void GetAllTable(string databaseName)
        {
            cbx_GetTables.Items.Clear();
            getAllTable(databaseName, out IntPtr tablenames);
            string strTables = Marshal.PtrToStringAnsi(tablenames);
            if (!string.IsNullOrEmpty(strTables))
            {
                string[] strTable = strTables.Split(' ');

                for (int i = 0; i < strTable.Length - 1; i++)
                {
                    if (!strTable[i].Equals("tb_users"))
                    {
                        cbx_GetTables.Items.Add(strTable[i]);
                    }
                }
                if (cbx_GetTables.Items.Count > 0)
                {
                    cbx_GetTables.SelectedIndex = 0;
                }

            }
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="tableName">数据库名</param>
        /// <returns></returns>
        private bool ConnectDatabase()
        {
            if (!initDBAPI("127.0.0.1", 3306, "root", "root"))
            {
                return false;
            }
            return true;
        }

        /// <summary>
        ///  查询某时间内的所有数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="timeName">添加时间的字段名</param>
        /// <param name="showCount">每页显示的数量</param>
        /// <param name="startTime">起始时间</param>
        /// <param name="endTime">终止时间</param>
        private void QueryDataByDate(string tableName, string timeName, int showCount, string startTime, string endTime)
        {
            DateTime dt_S = Convert.ToDateTime(startTime);
            DateTime dt_E = Convert.ToDateTime(endTime);

            if (DateTime.Compare(dt_S, dt_E) > 0)
            {
                MessageBox.Show("查询时间错误：起始时间大于终止时间！请重新查询", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                return;
            }
            else if (DateTime.Compare(dt_S, dt_E) == 0)
            {
                MessageBox.Show("查询时间错误：起始时间和终止时间相同！请重新查询", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                return;
            }
            if (showCount <= 0)
            {
                selectByTimeAPI(tableName, timeName, 100, startTime, endTime);
            }
            selectByTimeAPI(tableName, timeName, showCount, startTime, endTime);
        }

        /// <summary>
        /// 删除某时间段内的数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="timeName">添加时间的字段名</param>
        /// <param name="startTime">起始时间</param>
        /// <param name="endTime">终止时间</param>
        private void DeleteDataByDate(string tableName, string timeName, string startTime, string endTime)
        {
            DateTime dt_S = Convert.ToDateTime(startTime);
            DateTime dt_E = Convert.ToDateTime(endTime);


            if (DateTime.Compare(dt_S, dt_E) > 0)
            {
                MessageBox.Show("时间格式错误：起始时间大于终止时间！请重新查询", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                return;
            }
            else if (DateTime.Compare(dt_S, dt_E) == 0)
            {
                MessageBox.Show("时间格式错误：起始时间和终止时间相同！请重新查询", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                return;
            }
            deleteByTimeAPI(cbx_GetTables.Text, "addTime", dp_startTime.Value.ToString("yyyy-MM-dd HH:mm:ss.fff"), dp_endTime.Value.ToString("yyyy-MM-dd HH:mm:ss.fff"));
        }
        /// <summary>
        /// 按字段查询
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fieldName">字段名</param>
        /// <param name="showCount">每页显示的数量</param>
        /// <param name="minValue">查询范围最小值</param>
        /// <param name="maxValue">查询范围最大值</param>
        private void QueryDataByField(string tableName, string fieldName, int showCount, string minValue, string maxValue)
        {
            //selectByColAPI("tb_pointdata", "gap1", 10, ">=1.8767", "<=1.8767");
            if (showCount <= 0)
            {
                selectByColAPI(tableName, fieldName, 100, $">={minValue}", $"<={maxValue}");
            }
            selectByColAPI(tableName, fieldName, showCount, $">={minValue}", $"<={maxValue}");
        }
        /// <summary>
        /// 按字段删除
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fieldName">字段名</param>
        /// <param name="minValue">查询范围最小值</param>
        /// <param name="maxValue">查询范围最大值</param>
        private void DeleteDataByField(string tableName, string fieldName, string minValue, string maxValue)
        {
            //deleteByColAPI("tb_pointdata", "gap1", ">=1.8767", "<=1.8767");
            deleteByColAPI(tableName, fieldName, $">={minValue}", $"<={maxValue}");
        }
        private byte[] toData(string data)
        {
            byte[] encodedBytes = Encoding.UTF8.GetBytes(data);
            IntPtr pBuffer = Marshal.AllocHGlobal(encodedBytes.Length);//申请非托管内存
            Marshal.Copy(encodedBytes, 0, pBuffer, encodedBytes.Length);
            //Marshal.FreeHGlobal(pBuffer);
            return encodedBytes;
        }

        private void btn_Query_Click(object sender, EventArgs e)
        {
            if (cbx_GetDbs.SelectedItem == "" || cbx_GetTables.SelectedItem == "")
            {
                MessageBox.Show("未选取数据库或数据表", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                return;
            }
            if (Convert.ToInt32(numericUpDown1.Value) <= 0)
            {
                MessageBox.Show("显示行数不能为0", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                return;
            }
            else if (tabControl1.SelectedIndex == 0)
            {
                QueryDataByDate(cbx_GetTables.Text, "addTime", Convert.ToInt32(numericUpDown1.Value), dp_startTime.Value.ToString("yyyy-MM-dd HH:mm:ss.fff"), dp_endTime.Value.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            }
            else if (tabControl1.SelectedIndex == 1)
            {
                QueryDataByField(cbx_GetTables.Text, cbx_GetFields.Text, Convert.ToInt32(numericUpDown1.Value), txt_minValue.Text.Trim(), txt_maxValue.Text.Trim());
            }

        }

        private void btn_Delete_Click(object sender, EventArgs e)
        {
            if (cbx_GetDbs.SelectedIndex == -1 || cbx_GetTables.SelectedIndex == -1)
            {
                MessageBox.Show("未选取数据库或数据表", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                return;
            }
            else if (tabControl1.SelectedIndex == 0)
            {
                DeleteDataByDate(cbx_GetTables.Text, "addTime", dp_startTime.Value.ToString("yyyy-MM-dd HH:mm:ss.fff"), dp_endTime.Value.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            }
            else if (tabControl1.SelectedIndex == 1)
            {
                DeleteDataByField(cbx_GetTables.Text, cbx_GetFields.Text, txt_minValue.Text.Trim(), txt_maxValue.Text.Trim());
            }

        }

        private void Btn_QueryByField_Click(object sender, EventArgs e)
        {
            getAllCol(cbx_GetTables.Text, out IntPtr columnname);
            string strFileds = Marshal.PtrToStringAnsi(columnname);
            if (!string.IsNullOrEmpty(strFileds))
            {
                string[] strField = strFileds.Split(' ');

                for (int i = 0; i < strField.Length - 1; i++)
                {
                    cbx_GetFields.Items.Add(strField[i]);
                }
                cbx_GetFields.SelectedIndex = 0;
            }
        }

        int i = 0;
        private void FrmQtSql_Resize(object sender, EventArgs e)
        {
            ++i;
            if (++i > 1)
            {
                float newx = (this.Width) / X; //窗体宽度缩放比例
                float newy = (this.Height) / Y;//窗体高度缩放比例
                setControls(newx, newy, this);//随窗体改变控件大小                     
                setUISize(panel2.Width, panel2.Height);//适应分辨率
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (!openDBAPI(cbx_GetDbs.Text.Trim()))
            {
                this.lblStatus.ForeColor = Color.Red;
                return;
            }
            this.lblStatus.ForeColor = Color.Green;
            GetAllTable(cbx_GetDbs.Text.Trim());
        }

        private void cbx_GetDbs_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbx_GetTables.SelectedIndex = -1;
            cbx_GetTables.Items.Clear();
            lblStatus.ForeColor = Color.Black;
        }

        private void btn_QueryAll_Click(object sender, EventArgs e)
        {
            if (cbx_GetDbs.SelectedItem != "" && cbx_GetTables.SelectedItem != "")
            {
                if (Convert.ToInt32(numericUpDown1.Value) <= 0)
                {
                    MessageBox.Show("显示行数不能为0", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                    return;
                }
                selectAllAPI(cbx_GetTables.Text, Convert.ToInt32(numericUpDown1.Value));
            }
        }

    }
}
